MySQL バイナリログ
バイナリログとは
データベースへの変更をトランザクション完了順に記録したログ
ex）INSERTやUPDATEなどから、テーブルのメタデータ変更まで全て含む
トランザクションの開始および終了
値の挿入(INSERT)、値の更新(UPDATE)、削除(DELETE)
テーブル、インデックスの作成削除
原則として、壊れる直前までのデータを保持するようになってる
バイナリログへのディスク書き込みに遅延があるとそうでも無い
存在理由
レプリケーション
マスタサーバからスレーブサーバにbinlogの内容を転送します。スレーブサーバがbinlogを適用して、マスタサーバのデータと一致性を保ちます。
データベースのバックアップをベースに、バックアップ時点から指定時点の間のbinlogを適用することで、指定時点のデータを復元できます
すべての変更された記録はログファイルに書かれているため、まだデータベースのディスクに書き込まれていなくても復旧できる。
この場合変更後ログを使用しロールフォワードを行い最新の状態にまで復旧する
仕組みとして覚えておくべきこと
重要.icon バイナリログへの書き込みは、SQL文の実行が完了し、トランザクションをCOMMITした時に行われる
正確にはCOMMIT文が来たら、データの変更確定よりもバイナリログへの書き込みが先に行われる
注意：
sync_logの値によっては、即時でディスクに書き込まれるわけでもないので注意が必要
sync_log=0ならバックグラウンドスレッドが定期的に任意のタイミングでコミット済みステートメントをディスクに反映してくれる。
sync_log=1なら、1トランザクションが完了する度に反映
sync_log=100なら、100トランザクション完了する度に反映
sync_logが大きければ大きいほど、DBが壊れた時の復旧レベルが下がるので注意
ディスクに反映できなくて、直前のデータまで復旧できないとかになりうる
バイナリログはMySQL Server全体の機能であり、InnoDB専用ではない
なぜバイナリログが必要なのか？
バイナリログへの変更履歴の追加は、データをテーブルに書き込みよりもディスク効率が高い
なぜなら、バイナリログへの追記は最後尾にどんどん追加してくだけの簡単なお仕事
対して、テーブルへの書き込みは、ディスクへのランダムアクセスになってしまって余分な時間がかかる
バイナリログの書き込み種類
STATEMENT
SQL文そのものをバイナリログに書き込む形式
メリット
SQL文をそのまま書き込むので、他2つの方式に比べると書き込み量は多くならない
デメリット
非決定的なSQL文が存在した場合、レプリケーションや復元時に同じデータを得れなくなる。整合性が保てなくなる。
ROW
実行されたSQL文によるデータの変化を記録する
メリット
STATEMENTで言ってたような不整合が起きない
デメリット
データ量が多くなる
MIXED
非決定的なSQL文だけROWにして、あとはSTATEMENTに従うハイブリット型
メリット
良いとこどりができる
デメリット
ないかも？
注意点
バイナリログがファイルにCOMMITで書き込まれるまでは、それまでのSQL文はメモリ上に存在することになる。
トランザクションが長すぎると、スレッドが用意したバッファサイズを溜まったSQL文が圧迫し、遂には一時ファイルを利用してしまう。
こうなると性能が落ちるので注意されたし。
「binlog_cache_size」というパラメータを一時ファイルを利用しなくていいくらいの大きさに設定して、性能劣化を回避されたし。
binlog_cache_use, Binlog_cache_disk_useとか使って、監視しながら微調整しなさい。
sync_binlog
性能周りの話
binlogを利用するなら、トランザクションはなるべく小さくしたほうがいい。
理由：バッファにコミットまでのDML文が収まらない可能性が高くなり、もし溢れるとコミット途中で書き込みIOが発生してしまうから。
それによって書き込みクエリの速度低下をもたらすことになる。
トランザクションで実行するバイナリログ総量が、binlog_cache_sizeに収まる範囲にするとよい。
MySQLでbinlogを有効にする方法
参考